package dao;

import org.junit.Test;
import pojo.Goods;
import utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author konley
 * @date 2020-06-20 9:53
 */
public class GoodsDao {
    //根据名字模糊查商品结果集
    public List<Goods> getListByName(String comm){
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection con = DBUtils.getConnection();
        List<Goods> list = new ArrayList<Goods>();
        try {
            ps = con.prepareStatement("select * from goods where name like ?");
            ps.setString(1,"%"+comm+"%");
            rs = ps.executeQuery();
            while(rs.next()){
                Goods goods = new Goods(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("type"),
                        rs.getString("money"),
                        rs.getString("info"),
                        rs.getString("stock"),
                        rs.getString("url")
                );
                list.add(goods);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.release(rs,ps,con);
        }
        return list;
    }

    //根据id查商品
    public Goods getGoodsById(int id){
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection con = DBUtils.getConnection();
        Goods goods = null;
        try {
            ps = con.prepareStatement("select * from goods where id="+id);
            rs = ps.executeQuery();
            if(rs.next()){
                    goods = new Goods(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("type"),
                        rs.getString("money"),
                        rs.getString("info"),
                        rs.getString("stock"),
                        rs.getString("url")
                );
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.release(rs,ps,con);
        }
        return goods;
    }

    //添加商品
    public boolean add(Goods goods){
        PreparedStatement ps = null;
        Connection con = DBUtils.getConnection();
        boolean b = false;
        try {
            ps = con.prepareStatement("insert into goods value (null,?,?,?,?,?,?)");
            ps.setString(1,goods.getName());
            ps.setString(2,goods.getType());
            ps.setString(3,goods.getMoney());
            ps.setString(4,goods.getInfo());
            ps.setString(5,goods.getStock());
            ps.setString(6,goods.getUrl());
            b = ps.execute();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.release(ps,con);
        }
        return  b;
    }

    //根据名字精确查商品
    public Goods getGoodsByName(String name){
        PreparedStatement ps = null;
        ResultSet rs = null;
        Goods goods = null;
        Connection con = DBUtils.getConnection();
        List<Goods> list = new ArrayList<Goods>();
        try {
            ps = con.prepareStatement("select * from goods where name=?");
            ps.setString(1,name);
            rs = ps.executeQuery();
            if(rs.next()){
                goods = new Goods(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("type"),
                        rs.getString("money"),
                        rs.getString("info"),
                        rs.getString("stock"),
                        rs.getString("url")
                );
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.release(rs,ps,con);
        }
        return goods;
    }

    public boolean update(Goods goods){
        PreparedStatement ps = null;
        Connection con = DBUtils.getConnection();
        boolean b = false;
        try {
            ps = con.prepareStatement("update goods set name=?,type=?,money=?,info=?,stock=? where id=?");
            ps.setString(1,goods.getName());
            ps.setString(2,goods.getType());
            ps.setString(3,goods.getMoney());
            ps.setString(4,goods.getInfo());
            ps.setString(5,goods.getStock());
            ps.setInt(6,goods.getId());
            b = ps.execute();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.release(ps,con);
        }
        return b;
    }

    public boolean delete(int id){
        PreparedStatement ps = null;
        Connection con = DBUtils.getConnection();
        boolean b = false;
        try {
            ps = con.prepareStatement("delete from goods where id=?");
            ps.setInt(1,id);
            b = ps.execute();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtils.release(ps,con);
        }
        return b;
    }
}
